﻿CREATE PROCEDURE [maint].[HelpDb]
@DatabaseName [sysname]=NULL
AS
BEGIN

	DECLARE DB_CUR CURSOR FOR(
		SELECT [DbName]
		FROM [config].[DbMaintenance]
		WHERE ([DbName] = ISNULL(@DatabaseName,[DbName]))
	);
	OPEN DB_CUR;
	DECLARE @DbName sysname;
	FETCH NEXT FROM DB_CUR INTO @DbName;
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		PRINT REPLICATE('*',100);
		
		DECLARE @PrintOutput NVARCHAR(4000);
		SELECT @PrintOutput =
			N'Database: [' + [DbName] + N']'
			+ CHAR(10) + N'Enabled: ' + CASE [Enabled] WHEN 1 THEN N'Yes' ELSE N'No' END
		--	+ CHAR(10) + N'MaintenanceWindow: ' +
			+ CHAR(10) + N'BACKUPS'
			+ CHAR(10) + CHAR(9) + N'Previous Backup Devices: ' + CAST([BackupSets] AS NVARCHAR)
			+ CHAR(10) + CHAR(9) + N'Backup Agent: ' + CASE [BackupAgent] WHEN N'HBC' THEN N'Hyperbac' WHEN N'QLS' THEN N'Quest Litespeed' ELSE N'Native SQL Server' END
			+ CHAR(10) + CHAR(9) + N'FULL'
			+ CHAR(10) + CHAR(9) + CHAR(9) + N'Days: ' + [util].[GetWeekDaysFromBitmask]([FullBackupSchedule])
			+ CHAR(10) + CHAR(9) + CHAR(9) + N'Time: ' + LEFT(CONVERT(NCHAR(8),util.ChangeDateTime(GETDATE(),[FullBackupTime]),8),5)
			+ CHAR(10) + CHAR(9) + N'DIFFERENTIAL'
			+ CHAR(10) + CHAR(9) + CHAR(9) + N'Days: ' + [util].[GetWeekDaysFromBitmask]([DiffBackupSchedule])
			+ CHAR(10) + CHAR(9) + CHAR(9) + N'Time: ' + LEFT(CONVERT(NCHAR(8),util.ChangeDateTime(GETDATE(),[DiffBackupTime]),8),5)
			+ CHAR(10) + CHAR(9) + CHAR(9) + N'Frequency: every ' + CAST([DiffBackupFrequency] AS NVARCHAR) + N' hours'
			+ CHAR(10) + N'INDEX DEFRAGMENTATION'
			+ CHAR(10) + CHAR(9) + N'Logical Fragmentation Threshold: ' + CAST([DefragThreshold] AS NVARCHAR) + N'%'
			+ CHAR(10) + CHAR(9) + N'Days: ' + [util].[GetWeekDaysFromBitmask]([DefragWeekSchedule])
			+ CHAR(10) + CHAR(9) + N'Times: ' + [util].[GetHoursFromBitmask]([DefragDaySchedule])
			+ CHAR(10) + N'CONSISTANCY CHECKING'
			+ CHAR(10) + CHAR(9) + N'Days: ' + [util].[GetWeekDaysFromBitmask]([CheckDbWeekSchedule])
			+ CHAR(10) + CHAR(9) + N'Times: ' + [util].[GetHoursFromBitmask]([CheckDbDaySchedule])
			+ CHAR(10) + N'STATISTICS UPDATE'
			+ CHAR(10) + CHAR(9) + N'Times: ' + [util].[GetHoursFromBitmask]([UpdateStatsDaySchedule])
			+ CHAR(10) + CHAR(9) + N'Latency: ' + CAST([StatsLatency] AS NVARCHAR) + N' days'
		FROM [config].[DbMaintenance]
		WHERE ([DbName] = @DbName);
		
		PRINT @PrintOutput;

		FETCH NEXT FROM DB_CUR INTO @DbName;
	END;
	CLOSE DB_CUR;
	DEALLOCATE DB_CUR;
	
	PRINT REPLICATE('*',100);

END